"""
Case Type   : JDBC连接兼容B库
Case Name   : 公共函数
Create At   : 2023/10/13
Owner       : @ningyali
Description :
    1.公共函数
Expect      :
    1.公共函数
History     :
"""
import difflib
import os
import re
import subprocess
import tempfile
import textwrap
from datetime import datetime
from functools import wraps

from testcase.utils.CommonSH import CommonSH


class ComJDBC(CommonSH):
    def __init__(self, node_name='PrimaryDbUser'):
        super(ComJDBC, self).__init__(node_name)
        self.mysql_f_name = None
        self.og_f_name = None
        self.mysql_jar_name = None
        self.og_jar_name = None
        self.contant = ''
        self.pri_sh = CommonSH(node_name)
        self.update_list = ['create', 'alter', 'drop', 'insert', 'update',
                            'delete']
        self.query_list = ['select', 'show']
        self.exec_list = ['create procedure', 'call']
        self.cur_ip = subprocess.getoutput(
            "hostname -I|head -1|awk '{print $1}'")
        self.og_owned_param = ['dolphin.b_compatibility_mode',
                               'dolphin.sql_mode',
                               'bytea_output',
                               'enable_opfusion',
                               'enable_beta_opfusion',
                               'enable_bitmapscan',
                               'enable_seqscan',
                               'extra_float_digits',
                               'b_format_behavior_compat_options']
        self.err_ignore = [
            r'\[\d+\.\d+\.\d+\.\d+:\d+/\d+\.\d+\.\d+\.\d+:\d+\]',
            r'\*+ \d+,\d+ \*+', r'-+ \d+,\d+ -+',
            r'cost=?.*\s']
        self.err_ignore.extend([f'.*set\\s+{i}.*?\\n.*SQL SUCCESS\\n'
                                for i in self.og_owned_param])
        self.no_part_pattern = ['create procedure.*?begin.*?end;',
                                'create trigger.*?on.*?for each row.*?end;']
        self.html_pattern = r'<table class="diff" summary="Legends">\s+' \
                            r'<tr> <th colspan="2"> Legends </th> </tr>\s+' \
                            r'<tr> <td> <table border="" ' \
                            r'summary="Colors">\s+' \
                            r'<tr><th> Colors </th> </tr>\s+' \
                            r'<tr><td class="diff_add">&nbsp;Added&nbsp;' \
                            r'</td></tr>\s+' \
                            r'<tr><td class="diff_chg">Changed</td> </tr>\s+' \
                            r'<tr><td class="diff_sub">Deleted</td> </tr>\s+' \
                            r'</table></td>\s+' \
                            r'<td> <table border="" summary="Links">\s+' \
                            r'<tr><th colspan="2"> Links </th> </tr>\s+' \
                            r'<tr><td>\(f\)irst change</td> </tr>\s+' \
                            r'<tr><td>\(n\)ext change</td> </tr>\s+' \
                            r'<tr><td>\(t\)op</td> </tr>\s+' \
                            r'</table></td> </tr>\s+' \
                            r'</table>'

    def get_parent_dir(self, f_path, levels=1):
        self.log.info(f'原路径：{f_path}')
        for _ in range(levels):
            f_path = os.path.dirname(f_path)
        self.log.info(f'parent路径：{f_path}')
        return f_path

    def characteristic_sql(self, sql, *args):
        """
        根据传入sql生成个性化sql，以应对不同数据库系统变量等
        :param sql: 原sql语句
        :param args: 示例
            [['replace', 5, '111'],['add', 10, '222'],['delete', 12, '']]
        :return: 无
        """
        self.log.info('生成个性化sql')
        c_sql = sql.splitlines()
        for one in args:
            if one[1] < 0:
                index = len(c_sql) + one[1]
            else:
                index = one[1] - 1
            if one[0] == 'add':
                c_sql.insert(index, one[-1])
            elif one[0] == 'replace':
                c_sql[index] = one[-1]
            elif one[0] == 'delete':
                c_sql.pop(index)
            else:
                raise Exception(f'请检查入参：{one}')
        return "\n".join(c_sql)

    @staticmethod
    def read_sql_file(f_path):
        if os.path.exists(f_path):
            with open(f_path, 'r', encoding='utf-8', errors='ignore') as f:
                contant = f.read()
        else:
            raise Exception('文件不存在，请检查！')
        return contant

    def format_java_sql(self, sql, is_check_query_res_type):
        # 格式化成java文件可识别sql语句
        space = ' '
        f_sql = ''
        if sql.startswith('--'):
            f_sql += f'{space * 12}System.out.println("{sql}");\n'
        elif re.search('|'.join(self.query_list), sql.lower()) and not \
                re.search('|'.join(self.update_list), sql.lower()) and not \
                re.search('|'.join(self.exec_list), sql.lower()):
            f_sql += f'{space * 12}sql = "{sql}";\n' \
                f'{space * 12}QueryDataByObject(sql, conn, ' \
                f'{is_check_query_res_type});\n' \
                f'{space * 12}QueryDataByType(sql, conn, ' \
                f'{is_check_query_res_type});\n' \
                f'{space * 12}QueryDataByString(sql, conn);\n\n'
        elif re.search('|'.join(self.update_list), sql.lower()) and not \
                re.search('|'.join(self.exec_list), sql.lower()):
            f_sql += f'{space * 12}sql = "{sql}";\n' \
                f'{space * 12}executeUpdate(sql, conn);\n\n'
        else:
            f_sql += f'{space * 12}sql = "{sql}";\n' \
                f'{space * 12}execute(sql, conn);\n\n'

        return f_sql

    def part_sql_to_block(self, sql, is_part=True,
                          is_check_query_res_type=True):
        # 标志位为真时，按照;划分为一个个可执行语句，否则整块为可执行语句
        if not is_part:
            if '\n' not in sql:
                res_sql = self.format_java_sql(sql, is_check_query_res_type)
            else:
                tmp = f' " + "'.join([line.strip() for line in
                                      sql.strip().splitlines()])
                res_sql = self.format_java_sql(tmp, is_check_query_res_type)
        else:
            res_sql = ''
            for one in sql.strip().split(';'):
                if one and one.strip():
                    if '\n' not in one:
                        res_sql += self.format_java_sql(one,
                                                        is_check_query_res_type)
                    else:
                        tmp = f' " + "'.join([line.strip() for line in
                                              one.strip().splitlines()])
                        res_sql += self.format_java_sql(tmp,
                                                        is_check_query_res_type)

        return res_sql

    @staticmethod
    def part_sql_to_step(sql):
        # 按注释将sql文件划分为sql段
        steps = list()
        pattern = u"[\u4e00-\u9fa5]+"
        tmp_sql = re.split('--', sql)
        for one in tmp_sql:
            if one:
                one = one.replace('"', '\\"') + '\n'
                if one.strip().startswith('@') or \
                        one.strip().startswith('step') or \
                        re.match(pattern, one.strip()):
                    inner_tmp = '--' + one.strip()
                    print(inner_tmp)
                    if inner_tmp.count('--') == len(inner_tmp.splitlines()):
                        steps.append([inner_tmp, 'comment'])
                    else:
                        mid = len(re.match('--.*?\n', inner_tmp).group())
                        steps.append([inner_tmp[:mid].strip(), 'comment'])
                        steps.append([inner_tmp[mid:], 'sql'])
                else:
                    inner_tmp = one.strip()
                    steps.append([inner_tmp, 'sql'])
        return steps

    def part_sql_to_segment(self, sql):
        # 对sql语句进行正则，匹配到的语句块将作为整体，打上no part标记
        segments = list()
        pattern = [f'({i})' for i in self.no_part_pattern]
        regex_res = re.findall(r'|'.join(pattern), sql, re.I | re.S)
        if len(regex_res) == 0:
            segments.append([sql.strip(), 'part'])
        else:
            for one in regex_res:
                for inner in one:
                    self.log.info(inner)
                    if inner.strip():
                        tmp = sql.find(inner)
                        if tmp == -1:
                            # '没找到匹配项'
                            end = len(sql)
                            segments.append([sql[:end].strip(), 'part'])
                        else:
                            if tmp == 0:
                                # '从第一个字符开始匹配'
                                end = len(inner)
                                segments.append([sql[:end].strip(), 'no_part'])
                            else:
                                # '从中间位置开始匹配'
                                end = tmp + len(inner)
                                segments.append([sql[:tmp].strip(), 'part'])
                                segments.append([sql[tmp:end].strip(),
                                                 'no_part'])
                        sql = sql[end:]
            if sql != segments[-1]:
                segments.append([sql.strip(), 'part'])
        return segments

    def write_java_file(self, **kwargs):
        if kwargs["db_type"] == 'mysql':
            self.mysql_f_name = kwargs["f_name"]
        else:
            self.og_f_name = kwargs["f_name"]
        head = 'import java.sql.*;\n' \
               'import java.math.BigDecimal;\n' \
               'import java.text.SimpleDateFormat;\n' \
               'import java.io.ByteArrayOutputStream;\n' \
               'import java.io.InputStream;\n' \
               'import java.io.Reader;\n' \
               'import java.io.BufferedReader;\n' \
               'import java.io.IOException;\n' \
               'import java.nio.charset.StandardCharsets;\n' \
               'import java.util.Arrays;'
        space = ' '
        db_conn_info = f'jdbc:{kwargs["db_type"]}://{kwargs["db_host"]}:' \
            f'{kwargs["db_port"]}/{kwargs["db_name"]}'
        if kwargs.get('conn_params', None):
            db_conn_info += f'?{kwargs["conn_params"]}'
        declare_part = f'{space * 8}String url = "{db_conn_info}";\n' \
            f'{space * 8}String username = "{kwargs["db_user"]}";\n' \
            f'{space * 8}String password = "{kwargs["db_passwd"]}";\n' \
            f'{space * 8}String sql = "";'
        load_driver = f'{space * 12}Class.forName("{kwargs["db_driver"]}");' \
            if kwargs["db_type"] != 'opengauss' else ''
        conn_part = f'{space * 8}Connection conn = GetConnection(' \
            f'username, password, url);'
        conn_func = f'{space * 4}public static Connection GetConnection(' \
            f'String username, String passwd, String sourceURL) {{\n' \
            f'{space * 8}Connection conn = null;\n' \
            f'{space * 8}try {{\n' \
            f'{space * 12}conn = DriverManager.getConnection(' \
            f'sourceURL, username, passwd);\n' \
            f'{space * 12}System.out.println("Connection succeed");\n' \
            f'{space * 8}}} catch (Exception e) {{\n' \
            f'{space * 12}e.printStackTrace();\n' \
            f'{space * 12}return null;\n' \
            f'{space * 8}}}\n' \
            f'{space * 8}return conn;\n' \
            f'{space * 4}}}'
        query_by_object_func = f'{space * 4}public static void ' \
            f'QueryDataByObject(' \
            f'String sql, Connection conn, Boolean isCheckType) {{\n' \
            f'{space * 8}try {{\n' \
            f'{space * 12}System.out.println(sql) ;\n' \
            f'{space * 12}PreparedStatement ps = ' \
            f'conn.prepareStatement(sql);\n' \
            f'{space * 12}ResultSet rs = ps.executeQuery();\n' \
            f'{space * 12}ResultSetMetaData rsmd = rs.getMetaData();\n' \
            f'{space * 12}int cols = rsmd.getColumnCount();\n' \
            f'{space * 12}System.out.println("----getObject()获取查询结果----");\n' \
            f'{space * 12}int dRowNum = 0;\n' \
            f'{space * 12}String typeNames = "";\n' \
            f'{space * 12}String results = "";\n' \
            f'{space * 12}while (rs.next()) {{\n' \
            f'{space * 16}dRowNum++;\n' \
            f'{space * 16}for(int i=1; i<cols+1; i++) {{\n' \
            f'{space * 20}if (i==cols){{\n' \
            f'{space * 24}if (rs.getObject(i)!=null){{\n' \
            f'{space * 28}typeNames += rs.getObject(i).getClass() + "\\n";\n' \
            f'{space * 24}}} else {{\n' \
            f'{space * 28}typeNames += "null" + "\\n";\n' \
            f'{space * 24}}}\n' \
            f'{space * 24}results += rs.getObject(i) + "\\n";\n' \
            f'{space * 20}}} else {{\n' \
            f'{space * 24}if (rs.getObject(i)!=null){{\n' \
            f'{space * 28}typeNames += rs.getObject(i).getClass() + "|";\n' \
            f'{space * 24}}} else {{\n' \
            f'{space * 28}typeNames += "null" + "|";\n' \
            f'{space * 24}}}\n' \
            f'{space * 24}results += rs.getObject(i) + "|";\n' \
            f'{space * 20}}}\n' \
            f'{space * 16}}}\n' \
            f'{space * 12}}}\n' \
            f'{space * 12}System.out.print(typeNames);\n' \
            f'{space * 12}System.out.println(results);\n' \
            f'{space * 12}System.out.println();\n' \
            f'{space * 12}ps.close();\n' \
            f'{space * 8}}} catch (SQLException e) {{\n' \
            f'{space * 12}System.out.println("ERROR:" + e.getMessage());\n' \
            f'{space * 12}e.printStackTrace();\n' \
            f'{space * 8}}}\n' \
            f'{space * 4}}}'
        query_by_type_func = f'{space * 4}public static void ' \
            f'QueryDataByType(' \
            f'String sql, Connection conn, Boolean isCheckType) {{\n' \
            f'{space * 8}try {{\n' \
            f'{space * 12}System.out.println(sql) ;\n' \
            f'{space * 12}PreparedStatement ps = ' \
            f'conn.prepareStatement(sql);\n' \
            f'{space * 12}ResultSet rs = ps.executeQuery();\n' \
            f'{space * 12}ResultSetMetaData rsmd = rs.getMetaData();\n' \
            f'{space * 12}int cols = rsmd.getColumnCount();\n' \
            f'{space * 12}System.out.println("----getColumnType()获取查询结果类型' \
            f'----");\n' \
            f'{space * 12}if (isCheckType) {{\n' \
            f'{space * 16}for(int i=1; i<cols+1; i++) {{\n' \
            f'{space * 20}int colType = rsmd.getColumnType(i);\n' \
            f'{space * 20}if (i==cols){{\n' \
            f'{space * 24}System.out.println(colType);\n' \
            f'{space * 20}}} else {{\n' \
            f'{space * 24}System.out.print(colType + "|");\n' \
            f'{space * 20}}}\n' \
            f'{space * 16}}}\n' \
            f'{space * 12}}}\n' \
            f'{space * 12}while (rs.next()) {{\n' \
            f'{space * 16}for(int i=1; i<cols+1; i++) {{\n' \
            f'{space * 20}int colType = rsmd.getColumnType(i);\n' \
            f'{space * 20}String sres = formatString(colType, rs, i);\n' \
            f'{space * 20}if (i==cols){{\n' \
            f'{space * 24}System.out.println(sres);\n' \
            f'{space * 20}}} else {{\n' \
            f'{space * 24}System.out.print(sres + "|");\n' \
            f'{space * 20}}}\n' \
            f'{space * 16}}}\n' \
            f'{space * 12}}}\n' \
            f'{space * 12}System.out.println();\n' \
            f'{space * 12}ps.close();\n' \
            f'{space * 8}}} catch (SQLException e) {{\n' \
            f'{space * 12}System.out.println("ERROR:" + e.getMessage());\n' \
            f'{space * 12}e.printStackTrace();\n' \
            f'{space * 8}}}\n' \
            f'{space * 4}}}'
        query_by_string_func = f'{space * 4}public static void ' \
            f'QueryDataByString(String sql, Connection conn){{\n' \
            f'{space * 8}try {{\n' \
            f'{space * 12}System.out.println(sql) ;\n' \
            f'{space * 12}PreparedStatement ps = ' \
            f'conn.prepareStatement(sql);\n' \
            f'{space * 12}ResultSet rs = ps.executeQuery();\n' \
            f'{space * 12}ResultSetMetaData rsmd = rs.getMetaData();\n' \
            f'{space * 12}int cols = rsmd.getColumnCount();\n' \
            f'{space * 12}System.out.println("----getString()获取查询结果----");\n' \
            f'{space * 12}while (rs.next()) {{\n' \
            f'{space * 16}for(int i=1; i<cols+1; i++) {{\n' \
            f'{space * 20}if (i==cols){{\n' \
            f'{space * 24}System.out.println(rs.getString(i));\n' \
            f'{space * 20}}} else {{\n' \
            f'{space * 24}System.out.print(rs.getString(i) + "|");\n' \
            f'{space * 20}}}\n' \
            f'{space * 16}}}\n' \
            f'{space * 12}}}\n' \
            f'{space * 12}System.out.println();\n' \
            f'{space * 12}ps.close();\n' \
            f'{space * 8}}} catch (SQLException e) {{\n' \
            f'{space * 12}System.out.println("ERROR:" + e.getMessage());\n' \
            f'{space * 12}e.printStackTrace();\n' \
            f'{space * 8}}}\n' \
            f'{space * 4}}}'
        format_string_func = f'{space * 4}public static String formatString(' \
            f'int colType, ResultSet rs, int i){{\n' \
            f'{space * 8}String sres=null;\n' \
            f'{space * 8}try {{\n' \
            f'{space * 12}switch (colType) {{\n' \
            f'{space * 16}case Types.NULL:\n' \
            f'{space * 20}sres = "null";\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.NUMERIC:\n' \
            f'{space * 16}case Types.DECIMAL:\n' \
            f'{space * 20}BigDecimal res = rs.getBigDecimal(i);\n' \
            f'{space * 20}if (rs.wasNull()) {{\n' \
            f'{space * 24}sres = "null";\n' \
            f'{space * 20}}} else {{\n' \
            f'{space * 24}sres = res.toPlainString();\n' \
            f'{space * 20}}}\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.VARCHAR:\n' \
            f'{space * 16}case Types.CHAR:\n' \
            f'{space * 16}case Types.LONGVARCHAR:\n' \
            f'{space * 16}case Types.NCHAR:\n' \
            f'{space * 16}case Types.NVARCHAR:\n' \
            f'{space * 16}case Types.LONGNVARCHAR:\n' \
            f'{space * 20}sres = rs.getString(i);\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.TINYINT:\n' \
            f'{space * 16}case Types.SMALLINT:\n' \
            f'{space * 16}case Types.INTEGER:\n' \
            f'{space * 20}int intRes = rs.getInt(i);\n' \
            f'{space * 20}sres = Integer.toString(intRes);\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.BIGINT:\n' \
            f'{space * 20}long longRes = rs.getLong(i);\n' \
            f'{space * 20}sres = Long.toString(longRes);\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.FLOAT:\n' \
            f'{space * 20}float fres = rs.getFloat(i);\n' \
            f'{space * 20}sres = Float.toString(fres);\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.REAL:\n' \
            f'{space * 16}case Types.DOUBLE:\n' \
            f'{space * 20}double dres = rs.getDouble(i);\n' \
            f'{space * 20}sres = Double.toString(dres);\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.LONGVARBINARY:\n' \
            f'{space * 16}case Types.VARBINARY:\n' \
            f'{space * 16}case Types.BINARY:\n' \
            f'{space * 20}byte[] byteArrayRes = rs.getBytes(i);\n' \
            f'{space * 20}if (rs.wasNull()) {{\n' \
            f'{space * 24}sres = "null";\n' \
            f'{space * 20}}} else {{\n' \
            f'{space * 24}sres = new String(byteArrayRes);\n' \
            f'{space * 20}}}\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.DATE:\n' \
            f'{space * 20}Date mydate = rs.getDate(i);\n' \
            f'{space * 20}if (mydate != null) {{\n' \
            f'{space * 24}SimpleDateFormat dsdf = new SimpleDateFormat(' \
            f'"yyyy-MM-dd");\n' \
            f'{space * 24}sres = dsdf.format(mydate);\n' \
            f'{space * 20}}} else {{\n' \
            f'{space * 24}sres = "null";\n' \
            f'{space * 20}}}\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.TIME:\n' \
            f'{space * 20}Time mytime = rs.getTime(i);\n' \
            f'{space * 20}if (mytime != null) {{\n' \
            f'{space * 24}SimpleDateFormat tsdf = new SimpleDateFormat(' \
            f'"HH:mm:ss");\n' \
            f'{space * 24}sres = tsdf.format(mytime);\n' \
            f'{space * 20}}} else {{\n' \
            f'{space * 24}sres = "null";\n' \
            f'{space * 20}}}\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.TIMESTAMP:\n' \
            f'{space * 20}Timestamp myTimestamp = rs.getTimestamp(i);\n' \
            f'{space * 20}if (myTimestamp != null) {{\n' \
            f'{space * 24}SimpleDateFormat tssdf = new SimpleDateFormat(' \
            f'"yyyy-MM-dd HH:mm:ss");\n' \
            f'{space * 24}sres = tssdf.format(myTimestamp);\n' \
            f'{space * 20}}} else {{\n' \
            f'{space * 24}sres = "null";\n' \
            f'{space * 20}}}\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.CLOB:\n' \
            f'{space * 20}Clob myclob = rs.getClob(i);\n' \
            f'{space * 20}if (myclob != null) {{\n' \
            f'{space * 24}StringBuilder sb = new StringBuilder();\n' \
            f'{space * 24}Reader reader = myclob.getCharacterStream();\n' \
            f'{space * 24}BufferedReader br = new BufferedReader(reader);\n' \
            f'{space * 24}String line;\n' \
            f'{space * 24}while ((line = br.readLine()) != null) {{\n' \
            f'{space * 28}sb.append(line);\n' \
            f'{space * 24}}}\n' \
            f'{space * 24}sres = sb.toString();\n' \
            f'{space * 20}}} else {{\n' \
            f'{space * 24}sres = "null";\n' \
            f'{space * 20}}}\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.BIT:\n' \
            f'{space * 20}sres = rs.getString(i);\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.BOOLEAN:\n' \
            f'{space * 20}sres = Boolean.toString(rs.getBoolean(i));\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.BLOB:\n' \
            f'{space * 20}Blob myblob = rs.getBlob(i);\n' \
            f'{space * 20}if (myblob != null) {{\n' \
            f'{space * 24}InputStream inputStream = ' \
            f'myblob.getBinaryStream();\n' \
            f'{space * 24}ByteArrayOutputStream outputStream = new ' \
            f'ByteArrayOutputStream();\n' \
            f'{space * 24}byte[] buffer = new byte[4096];\n' \
            f'{space * 24}int bytesRead = -1;\n' \
            f'{space * 24}try {{\n' \
            f'{space * 28}while ((bytesRead = inputStream.read(' \
            f'buffer)) != -1) {{\n' \
            f'{space * 32}outputStream.write(buffer, 0, bytesRead);\n' \
            f'{space * 28}}}\n' \
            f'{space * 24}}} catch (Exception e) {{\n' \
            f'{space * 28}throw new SQLException("' \
            f'Failed to read BLOB data.", e);\n' \
            f'{space * 24}}}\n' \
            f'{space * 24}byte[] blobBytes = outputStream.toByteArray();\n' \
            f'{space * 24}sres = new String(blobBytes, ' \
            f'StandardCharsets.UTF_8);\n' \
            f'{space * 20}}} else {{\n' \
            f'{space * 24}sres = "null";\n' \
            f'{space * 20}}}\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.ARRAY:\n' \
            f'{space * 20}Array myArray = rs.getArray(i);\n' \
            f'{space * 20}sres = Arrays.toString((Object[]) ' \
            f'myArray.getArray());\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}case Types.REF:\n' \
            f'{space * 20}Ref ref = rs.getRef(i);\n' \
            f'{space * 20}Object obj = ref.getObject();\n' \
            f'{space * 20}sres = obj.toString();\n' \
            f'{space * 20}break;\n' \
            f'{space * 16}default:\n' \
            f'{space * 20}sres = rs.getString(i);\n' \
            f'{space * 20}break;\n' \
            f'{space * 12}}}\n' \
            f'{space * 8}}} catch (SQLException e) {{\n' \
            f'{space * 12}System.out.println("ERROR:" + e.getMessage());\n' \
            f'{space * 12}e.printStackTrace();\n' \
            f'{space * 8}}} catch (IOException e) {{\n' \
            f'{space * 12}System.out.println("ERROR:" + e.getMessage());\n' \
            f'{space * 12}e.printStackTrace();\n' \
            f'{space * 8}}}\n' \
            f'{space * 8}return sres;\n' \
            f'{space * 4}}}'
        exec_update_func = f'{space * 4}public static void executeUpdate(' \
            f'String sql, Connection conn) {{\n' \
            f'{space * 8}try {{\n' \
            f'{space * 12}System.out.println(sql) ;\n' \
            f'{space * 12}Statement stmt = conn.createStatement();\n' \
            f'{space * 12}int rc = stmt.executeUpdate(sql);\n' \
            f'{space * 12}if (rc>=0) {{\n' \
            f'{space * 16}System.out.println("SQL SUCCESS");\n' \
            f'{space * 12}}}\n' \
            f'{space * 8}}} catch (SQLException e) {{\n' \
            f'{space * 12}System.out.println("ERROR:" + e.getMessage());\n' \
            f'{space * 12}e.printStackTrace();\n' \
            f'{space * 8}}}\n' \
            f'{space * 4}}}'
        exec_func = f'{space * 4}public static void execute(' \
            f'String sql, Connection conn) {{\n' \
            f'{space * 8}try {{\n' \
            f'{space * 12}System.out.println(sql) ;\n' \
            f'{space * 12}Statement stmt = conn.createStatement();\n' \
            f'{space * 12}Boolean rb = stmt.execute(sql);\n' \
            f'{space * 12}System.out.println("SQL SUCCESS");\n' \
            f'{space * 8}}} catch (SQLException e) {{\n' \
            f'{space * 12}System.out.println("ERROR:" + e.getMessage());\n' \
            f'{space * 12}e.printStackTrace();\n' \
            f'{space * 8}}}\n' \
            f'{space * 4}}}'
        sql_part = ''
        f_steps = self.part_sql_to_step(kwargs["sql"])
        for step in f_steps:
            if step[1] == 'comment':
                sql_part += self.format_java_sql(step[0], kwargs[
                    "is_check_query_res_type"])
            else:
                f_segments = self.part_sql_to_segment(step[0])
                for segment in f_segments:
                    if segment[1] == 'part':
                        sql_part += self.part_sql_to_block(
                            segment[0],
                            is_part=True,
                            is_check_query_res_type=kwargs[
                                "is_check_query_res_type"])
                    else:
                        sql_part += self.part_sql_to_block(
                            segment[0],
                            is_part=False,
                            is_check_query_res_type=kwargs[
                                "is_check_query_res_type"])
        catch_class_not_found_exception = f'' \
            f'catch (ClassNotFoundException e) {{\n' \
            f'{space * 12}e.printStackTrace();\n' \
            f'{space * 8}}}' if kwargs["db_type"] != 'opengauss' else ''
        self.contant = f'{head}\n\n' \
            f'public class {kwargs["f_name"]} {{\n' \
            f'{space * 4}public static void main(String[] args) {{\n' \
            f'{declare_part}\n\n' \
            f'{conn_part}\n' \
            f'{space * 8}try {{\n{load_driver}\n{sql_part}\n' \
            f'{space * 8}}} {catch_class_not_found_exception} ' \
            f'catch (Exception e) {{\n' \
            f'{space * 12}e.printStackTrace();\n' \
            f'{space * 8}}} finally {{\n' \
            f'{space * 12}try {{\n' \
            f'{space * 16}if (conn != null) {{conn.close();}}\n' \
            f'{space * 12}}} catch (SQLException e) {{\n' \
            f'{space * 16}e.printStackTrace();\n' \
            f'{space * 12}}}\n' \
            f'{space * 8}}}\n' \
            f'{space * 4}}}\n\n' \
            f'{conn_func}\n\n' \
            f'{query_by_object_func}\n\n' \
            f'{query_by_type_func}\n\n' \
            f'{format_string_func}\n\n' \
            f'{query_by_string_func}\n\n' \
            f'{exec_update_func}\n\n' \
            f'{exec_func}\n' \
            f'}}'
        self.log.info(self.contant)
        with open(f'{kwargs["f_name"]}.java', 'w+', encoding='utf-8',
                  errors='ignore') as f:
            f.write(self.contant)

    def compile_java_file(self, db_type):
        jar_name = self.mysql_jar_name if db_type == 'mysql' else \
            self.og_jar_name
        f_name = self.mysql_f_name if db_type == 'mysql' else self.og_f_name
        cmd = f'javac -encoding utf-8 -cp {jar_name} {f_name}.java'
        self.log.info(cmd)
        res = subprocess.getoutput(cmd)
        self.log.info(res)
        return res

    def run_java_file(self, db_type):
        jar_name = self.mysql_jar_name if db_type == 'mysql' else \
            self.og_jar_name
        f_name = self.mysql_f_name if db_type == 'mysql' else self.og_f_name
        start = datetime.now()
        cmd = f'java -cp {jar_name}:./ {f_name}'
        res, err = self.getoutput(cmd)
        end = datetime.now()
        run_time = (end - start).seconds
        return res, err, run_time

    def compare_sql_res(self, from_str, to_str, from_name, to_name, f_name):
        diff = difflib.context_diff(from_str.splitlines(),
                                    to_str.splitlines(),
                                    fromfile=from_name,
                                    tofile=to_name)
        str_diff = '\n'.join([i.strip() for i in diff])
        if len(str_diff.strip()) > 0:
            self.log.info('\n' + str_diff)
            self.log.info('-----内容不一致，生成html格式的对比文件-----')
            diff_log_path = os.path.join('result', f_name)
            format_from = '\n'.join([textwrap.fill(line, width=80) for line in
                                     from_str.splitlines()])
            format_to = '\n'.join([textwrap.fill(line, width=80) for line in
                                   to_str.splitlines()])
            source_list = format_from.splitlines()
            target_list = format_to.splitlines()
            old_str = 'charset=ISO-8859-1'
            new_str = 'charset=UTF-8'
            s_len = len(source_list)
            t_len = len(target_list)
            max_length = s_len if s_len > t_len else t_len
            start_pos = 0
            step = 1000
            end_pos = start_pos + step
            diff = difflib.HtmlDiff()
            detail_info = ''
            while end_pos < max_length + step:
                detail_info += diff.make_file(source_list[start_pos: end_pos],
                                              target_list[start_pos: end_pos],
                                              from_name, to_name)
                start_pos = end_pos
                end_pos = start_pos + step
            regex = re.findall(self.html_pattern,
                               detail_info.replace(old_str, new_str))
            new_contant = re.sub(self.html_pattern, '<br><br>',
                                 detail_info.replace(old_str, new_str),
                                 len(regex) - 1)
            with open(diff_log_path, 'w+', encoding='utf-8',
                      errors='ignore') as f:
                f.write(new_contant)
        return str_diff

    def compare_sql_err_res(self, from_str, to_str, from_name, to_name,
                            html_diff_file_name, *ignore):
        self.log.info('-----忽略不重要信息-----')
        self.err_ignore.extend(ignore)
        for pattern in self.err_ignore:
            regex_res1 = re.sub(pattern, "", from_str)
            regex_res2 = re.sub(pattern, "", to_str)
            from_str = regex_res1
            to_str = regex_res2

        str_diff = self.compare_sql_res(from_str, to_str, from_name, to_name,
                                        html_diff_file_name)
        return str_diff

    def remove_java_file(self):
        remove_list = list()
        if self.mysql_f_name:
            remove_list.append(f'{self.mysql_f_name}.java')
            remove_list.append(f'{self.mysql_f_name}.class')
        if self.og_f_name:
            remove_list.append(f'{self.og_f_name}.java')
            remove_list.append(f'{self.og_f_name}.class')
        if self.og_jar_name:
            remove_list.append(self.og_jar_name)
        if self.mysql_jar_name:
            remove_list.append(self.mysql_jar_name)
        for f in remove_list:
            f_path = os.path.join(os.getcwd(), f)
            if os.path.exists(f_path):
                os.remove(f_path)

    def get_jdbc(self, db_type):
        cmd = f"yat info|grep 'Yat home path'|awk -F ':' '{{print $2}}'"
        self.log.info(cmd)
        yat_home_path = subprocess.getoutput(cmd)
        lib_path = os.path.join(yat_home_path, 'lib')
        self.log.info(lib_path)
        cmd = f"ls {lib_path}|grep '{db_type}'|head -1"
        self.log.info(cmd)
        jar_name = subprocess.getoutput(cmd)
        self.log.info(jar_name)
        if jar_name:
            if db_type == 'mysql':
                self.mysql_jar_name = jar_name
            else:
                self.og_jar_name = jar_name
            cmd = f"\\cp {os.path.join(lib_path, jar_name)} ."
            self.log.info(cmd)
            res = subprocess.getoutput(cmd)
            self.log.info(res)
        else:
            res = None
            self.log.error(f"未找到{db_type}jar包")
        return res

    def getoutput(self, cmd, ispwd=False, is_print=True):
        if not ispwd:
            self.log.info(f'开始执行: {cmd.strip()}')
        f_out = None
        f_err = None
        tem_out = tempfile.TemporaryFile()
        tem_err = tempfile.TemporaryFile()
        try:
            sub_obj = subprocess.Popen(cmd,
                                       shell=True,
                                       encoding="utf-8",
                                       stdout=tem_out,
                                       stderr=tem_err)
            sub_obj.communicate()
            tem_out.seek(0)
            f_out = tem_out.read().decode('utf-8')
            tem_err.seek(0)
            f_err = tem_err.read().decode('utf-8')
            if f_err:
                self.log.error(f'Error: {f_err}')
            if is_print:
                self.log.info(f'Success: {f_out}')
        except Exception as e:
            self.log.error(f'Error: {str(e)}')
        finally:
            tem_out.close()
            tem_err.close()
        return f_out.strip(), f_err.strip()

    @staticmethod
    def compare_version(v1, v2):
        """
        比较传入的两个版本号V1,V2
        :param v1: 版本号1
        :param v2: 版本号2
        :return: V1>=v2返回True，否则返回False
        """
        v1_check = re.match(r"\d+(\.\d+){0,2}", v1)
        v2_check = re.match(r"\d+(\.\d+){0,2}", v2)
        if v1_check is None or v2_check is None \
                or v1_check.group() != v1 \
                or v2_check.group() != v2:
            return "版本号格式不对，正确的应该是x.x.x,只能有3段"
        v1_list = v1.split(".")
        v2_list = v2.split(".")
        v1_len = len(v1_list)
        v2_len = len(v2_list)
        if v1_len > v2_len:
            for i in range(v1_len - v2_len):
                v2_list.append("0")
        elif v2_len > v1_len:
            for i in range(v2_len - v1_len):
                v1_list.append("0")
        else:
            pass
        for i, _ in enumerate(v1_list):
            if int(v1_list[i]) > int(v2_list[i]):
                return True
            if int(v1_list[i]) < int(v2_list[i]):
                return False
        return True

    def skip_if_version_incorrect(self, mysql_node, expect_version):
        def my_decorator(func):
            @wraps(func)
            def wrapped_function(*args, **kwargs):
                self.log.info('获取mysql版本,低于指定版本则跳过用例执行')
                get_version_cmd = f'cd ' \
                    f'{os.path.dirname(mysql_node.bin_path)}; bin/mysql -V'
                self.log.info(get_version_cmd)
                vesion_res = mysql_node.sh(get_version_cmd).result()
                self.log.info(vesion_res)
                regex_res = re.search(r'\d+\.\d+\.\d+', vesion_res)
                self.log.info(regex_res)
                self.mysql_version = regex_res.group() if regex_res else None
                if self.mysql_version:
                    if self.compare_version(self.mysql_version,
                                            expect_version):
                        self.log.info(f"mysql版本适配,执行用例")
                        return func(*args, **kwargs)
                    else:
                        self.log.info(f"mysql版本低于{expect_version},跳过执行")
                        return
                else:
                    self.log.info("获取mysql版本失败,跳过执行")
                    return

            return wrapped_function

        return my_decorator

    def compare_run_time(self, f_runtime, s_runtime, f_name, s_name):
        if f_runtime > s_runtime:
            t_diff = (f_runtime - s_runtime) / f_runtime * 100
            des = f'{s_name}性能更优'
            self.log.info(f'{des}，耗时{s_runtime}s, '
                          f'{f_name}耗时{f_runtime}s, 相差{round(t_diff, 3)}%')
        elif f_runtime == s_runtime:
            t_diff = 0
            des = f'{f_name}与{s_name}性能一致'
            self.log.info(f'{des}，耗时{s_runtime}')
        else:
            t_diff = (s_runtime - f_runtime) / s_runtime * 100
            des = f'{f_name}性能更优'
            self.log.info(f'{des}，耗时{f_runtime}s, '
                          f'{s_name}耗时{s_runtime}s, 相差{round(t_diff, 3)}%')
        return t_diff, des
